/**
 * Copyright(c) by JNet Smartware Group.
 * All rights reserved. Not distribution or redistribution without
 * any permission of JNet Smartware Group.
 * 
 * @author thuc
 * 2010/03/15
 */
package vn.smartware.co.server.dao.search;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import vn.smartware.co.dto.MstCustomerDTO;
import vn.smartware.co.dto.MstDrinkDTO;
import vn.smartware.co.dto.MstFoodDTO;
import vn.smartware.co.dto.MstPriceDTO;
import vn.smartware.co.dto.MstRoomDTO;
import vn.smartware.co.dto.MstRoomPriceDTO;
import vn.smartware.co.framework.bean.LoginDTO;
import vn.smartware.co.framework.bean.AddressDTO;
import vn.smartware.co.framework.exception.ServerException;
import vn.smartware.co.framework.form.CommonSearchForm;
import vn.smartware.co.java.net.commons.Utils;
import vn.smartware.co.server.dao.factory.AbstractCommonSearchDAO;
import vn.smartware.co.server.sql.factory.AbstractCommonSearchSQLManager;
import vn.smartware.co.server.sql.factory.SQLFactory;

/**
 * @author thuctt
 * CommonSearchDAO
 */
@SuppressWarnings({"rawtypes","unchecked"})
public class CommonSearchDAO extends AbstractCommonSearchDAO {

	private static Log log = LogFactory.getLog(CommonSearchDAO.class);
	
	/**
	 * get drink list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList getDrinkList(CommonSearchForm form, LoginDTO user,
			Connection conn) throws ServerException {
		
		AbstractCommonSearchSQLManager manager = SQLFactory.createCommonSearchSQLManager();
		ResultSet rs = null;
		int count = 0;
		ArrayList drinkList = new ArrayList();
		ArrayList priceList = new ArrayList();
		MstDrinkDTO drink = new MstDrinkDTO();
		try {
			manager.makeGetDrinkListSQL(form, user, conn);
			rs = manager.executeQuery();
			
			String preDrinkNo = "";
			while (rs.next()){
				String drinkNo = rs.getString("DRINK_NO");
				if (!Utils.isNullEmpty(preDrinkNo) && !drinkNo.equals(preDrinkNo)){
					drink.setPriceList(priceList);
					drinkList.add(drink);
					drink = new MstDrinkDTO();
					priceList = new ArrayList();
				}
				
				drink.setDrinkNo(rs.getString("DRINK_NO"));
				drink.setName(rs.getString("NAME"));
				drink.setDescription(rs.getString("DESCRIPTION"));
				drink.setType(rs.getString("DRINK_TYPE"));
				drink.setTypeName(rs.getString("DRINK_TYPE_NAME"));
				drink.setUnitCd(rs.getString("UNIT_CD"));
				drink.setUnitName(rs.getString("UNIT_NAME"));
				
				String priceNo = Utils.toEmpty(rs.getString("PRICE_NO"));
				if (!Utils.isNullEmpty(priceNo)){
					MstPriceDTO price = new MstPriceDTO();
					price.setPriceNo(rs.getString("PRICE_NO"));
					price.setPrice(rs.getString("PRICE"));
					price.setMoneyType(rs.getString("MONEY_TYPE"));
					price.setMoneyTypeName(rs.getString("MONEY_TYPE_NAME"));
					priceList.add(price);
				}
				
				preDrinkNo = drinkNo;
				count++;
			}
			
			log.info(count + " records.");

			if (count > 0){
				drink.setPriceList(priceList);
				drinkList.add(drink);
			}
			
			return drinkList;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * get food list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList getFoodList(CommonSearchForm form, LoginDTO user,
			Connection conn) throws ServerException {
		
		AbstractCommonSearchSQLManager manager = SQLFactory.createCommonSearchSQLManager();
		ResultSet rs = null;
		int count = 0;
		ArrayList foodlist = new ArrayList();
		ArrayList priceList = new ArrayList();
		MstFoodDTO food = new MstFoodDTO();
		try {
			manager.makeGetFoodListSQL(form, user, conn);
			rs = manager.executeQuery();
			
			String preFoodNo = "";
			while (rs.next()){
				String foodNo = rs.getString("FOOD_NO");
				if (!Utils.isNullEmpty(preFoodNo) && !foodNo.equals(preFoodNo)){
					food.setPriceList(priceList);
					foodlist.add(food);
					food = new MstFoodDTO();
					priceList = new ArrayList();
				}
				
				food.setFoodNo(rs.getString("FOOD_NO"));
				food.setName(rs.getString("NAME"));
				food.setDescription(rs.getString("DESCRIPTION"));
				food.setUnitCd(rs.getString("UNIT_CD"));
				food.setUnitName(rs.getString("UNIT_NAME"));
				
				String priceNo = Utils.toEmpty(rs.getString("PRICE_NO"));
				if (!Utils.isNullEmpty(priceNo)){
					MstPriceDTO price = new MstPriceDTO();
					price.setPriceNo(rs.getString("PRICE_NO"));
					price.setPrice(rs.getString("PRICE"));
					price.setMoneyType(rs.getString("MONEY_TYPE"));
					price.setMoneyTypeName(rs.getString("MONEY_TYPE_NAME"));
					priceList.add(price);
				}
				
				preFoodNo = foodNo;
				count++;
			}
			
			log.info(count + " records.");

			if (count > 0){
				food.setPriceList(priceList);
				foodlist.add(food);
			}
			
			return foodlist;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
	/**
	 * get room list
	 * @param form 
	 * @param user 
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	public ArrayList getRoomList(CommonSearchForm form, LoginDTO user,
			Connection conn) throws ServerException {
		
		AbstractCommonSearchSQLManager manager = SQLFactory.createCommonSearchSQLManager();
		ResultSet rs = null;
		int count = 0;
		ArrayList roomList = new ArrayList();
		ArrayList priceList = new ArrayList();
		MstRoomDTO room = new MstRoomDTO();
		try {
			manager.makeGetRoomListSQL(form, user, conn);
			rs = manager.executeQuery();
			
			String preRoomNo = "";
			while (rs.next()){
				String roomNo = rs.getString("ROOM_NO");
				if (!Utils.isNullEmpty(preRoomNo) && !roomNo.equals(preRoomNo)){
					room.setPriceList(priceList);
					roomList.add(room);
					room = new MstRoomDTO();
					priceList = new ArrayList();
				}
								
				room.setRoomNo(roomNo);
				room.setRoomName(rs.getString("ROOM_NAME"));
				room.setRoomType(rs.getString("ROOM_TYPE"));
				room.setRoomTypeName(rs.getString("ROOM_TYPE_NAME"));
				room.setStatus(rs.getString("STATUS"));
				room.setStatusName(rs.getString("STATUS_NAME"));
				room.setDescription(rs.getString("DESCRIPTION"));
				
				String priceNo = Utils.toEmpty(rs.getString("PRICE_NO"));
				if (!Utils.isNullEmpty(priceNo)){
					MstRoomPriceDTO price = new MstRoomPriceDTO();
					price.setPriceNo(rs.getString("PRICE_NO"));
					price.setRentType(rs.getString("RENT_TYPE"));
					price.setRentTypeName(rs.getString("RENT_TYPE_NAME"));
					price.setPrice(rs.getString("PRICE"));
					price.setMoneyType(rs.getString("MONEY_TYPE"));
					price.setMoneyTypeName(rs.getString("MONEY_TYPE_NAME"));
					
					priceList.add(price);
				}
				
				preRoomNo = roomNo;
				count++;
			}
		
			log.info(count + " records.");

			if (count > 0){
				room.setPriceList(priceList);
				roomList.add(room);
			}
			
			return roomList;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}

	/**
	 * getCustomerList
	 * @param form
	 * @param user
	 * @param conn
	 * @return ArrayList
	 * @throws ServerException
	 */
	@Override
	public ArrayList getCustomerList(CommonSearchForm form, LoginDTO user,
			Connection conn) throws ServerException {
		
		AbstractCommonSearchSQLManager manager = SQLFactory.createCommonSearchSQLManager();
		ResultSet rs = null;
		int count = 0;
		ArrayList list = new ArrayList();
		try {
			manager.makeGetCustomerListSQL(form, user, conn);
			rs = manager.executeQuery();
			
			while (rs.next()){
				MstCustomerDTO cus = new MstCustomerDTO();
				cus.setCustomerNo(Utils.toEmpty(rs.getString("CUSTOMER_NO")));
				cus.setName(Utils.toEmpty(rs.getString("NAME")));
				cus.setBirthday(Utils.toEmpty(rs.getString("BIRTHDAY")));
				cus.setIdNumber(Utils.toEmpty(rs.getString("ID_NUMBER")));
				cus.setTel(Utils.toEmpty(rs.getString("TEL")));
				cus.setEmail(Utils.toEmpty(rs.getString("EMAIL")));
				
				String addressCd = Utils.toEmpty(rs.getString("ADDRESS_CD"));
				if (!Utils.isNullEmpty(addressCd)){
					AddressDTO addr = new AddressDTO();
					addr.setAddressCd(Integer.parseInt(addressCd));
					addr.setHouseNo(Utils.toEmpty(rs.getString("HOUSE_NO")));
					addr.setStreet(Utils.toEmpty(rs.getString("STREET")));
					addr.setWard(Utils.toEmpty(rs.getString("WARD")));
					addr.setDistrict(Utils.toEmpty(rs.getString("DISTRICT")));
					addr.setCity(Utils.toEmpty(rs.getString("CITY")));
					addr.setNationality(Utils.toEmpty(rs.getString("NATIONALITY")));
					
					cus.setAddress(addr);
				}
				
				list.add(cus);
				count++;
			}
		
			log.info(count + " records.");
			return list;
			
		} catch (SQLException e) {
			log.error("SQLException: ", e);
			throw new ServerException(e.getMessage());
		} finally {
			try {
				manager.close(rs);
				manager.close();
				
			} catch (SQLException e) {
				log.error("SQLException: ", e);
				throw new ServerException(e.getMessage());
			}
		}
	}
	
}
